کار با مفاهیم آدرس دهی نسبی و مطلق در اکسل
همانطور که می دانید نرمافزار اکسل از صفحات کاری متعددی موسوم به worksheet تشکیل شده است.
هر worksheet نیز به نوبه خود از تعداد زیادی سطر و ستون تشکیل شده و هرکدام از این سطرها و ستونها نیز خود متشکل از سلول ها می باشد.
مفاهیم آدرس دهی نسبی و مطلق در اکسل بدین معناست که موقعیت یک یا چند سلول را در بین سطرها و ستون های موجود در صفحه کاری worksheet تعیین کنیم.
نرم افزار اکسل جهت نامگذاری سطرها از اعداد و برای نام گذاری ستون ها از حروف الفبای انگلیسی استفاده می کند. بعنوان مثال سلولی با مختصات A10 سلولی است که در اولین ستون از صفحه کاری و در ردیف دهم آن قرار دارد .
و یا عبارتA2:D5 اشاره به محدوده ای از سلول ها دارد که نقطه شروع آنها در ردیف دوم از ستون اول و سلول انتهایی آن در ردیف پنجم از ستون D واقع است.
یکی از قابلیت های نرم افزار اکسل این است که شما می توانید محتویات یک سلول را با استفاده از آدرس آن سلول به سایر قسمتها در اکسل ارجاع دهید.
به عنوان مثال اگر در سلول مربوط به D5 عبارت A2 = را درج کنید در واقع سلول D5 را به سلول A2 ارجاع می دهید تا مقادیر موجود در سلول D5 برابر با مقادیر موجود در سلول A2 باشد.
به کل این فرآیند در نرم افزار اکسل عنوان ارجاع دادن گفته می شود .
آنچه که در این مطلب آموزشی به دنبال آن خواهیم بود این است که با مفاهیم آدرس دهی نسبی و مطلق در اکسل در نرم افزار اکسل به طور کامل آشنا شده و با نحوه کاربرد و استفاده از آنها در موقعیت های مختلف به درستی آشنا شویم.
انواع مختلف آدرس دهی در نرم افزار اکسل
به طور کلی در نرم افزار اکسل می توان به سه طریق مختلف نسبت به آدرس دهی سلولها اقدام نمود که آشنایی با نحوه ی عملکرد این ۳ نوع آدرس دهی می تواند بازدهی بالایی را برای شما در فرمول نویسی در نرم افزار اکسل به همراه داشته باشد .
- روش اول ارجاع نسبی
اجازه بدهید برای توضیح مفهوم ارجاع نسبی از یک مثال کاربردی استفاده نماییم.
فرض نمایید که اطلاعاتی در اکسل شامل موارد زیر را در اختیار دارید.
مطلب خواندنی : افزودن خط میانگین به نمودار اکسل
هدف ما این است که کل مبلغ فروش برای هر کالا را که حاصل ضرب تعداد کالای فروش رفته در مبلغ فروش هر کالا است را محاسبه نموده و در ستون مربوط به مبلغ کل در جلو هر کالا محاسبه و درج کنیم.
کاری که می کنیم این است که کل مبلغ فروش برای کالای” پرینتر ” را از طریق درج فرمول در سلول مربوط به مبلغ کل برای این کالا وارد نموده و مبلغ کلی فروش برای این کالا را محاسبه نماییم.
اکنون به جای اینکه همین کار را برای سایر کالاها به صورت مجزا انجام بدهیم می توانیم فرمول وارد شده برای کالای اول را با استفاده از خاصیت Drag & Drop برای سایر کالاها نیز تعمیم بدهیم.
بعد از انجام این کار شما ملاحظه خواهید نمود که نرم افزار اکسل به طور خودکار آدرس مربوط به سلولهای مبلغ کالا و تعداد فروش هر کالا را جایگزین آدرس اولیه خود می نماید.
اتفاقی که میافتد این است که نرم افزار اکسل آدرس سلولهایی را که بر مبنای آن محاسبات خود را انجام می دهد بر اساس موقعیت جدید که فرمول در آن واقع شده است تغییر می دهد.
بدین معنا که وقتی که ما فرمول را در ردیف مربوط به کالای دوم وارد می نمایم اکسل به طور خودکار از سلولهای مربوط به تعداد کالای ” لپ تاپ” و مبلغ مربوط به قیمت لپ تاپ موجود در ردیف دوم که فرمول در آن وارد شده است استفاده می نماید.
بنابراین با استفاده از این خاصیت ، فرمول به راحتی می تواند خود را با موقعیت جدید تطبیق داده و وابسته به اطلاعات قبلی خود نباشد.
این خاصیتی است که از طریق آدرس دهی نسبی در نرم افزار اکسل ایجاد شده است.
بنابراین اگر بخواهیم آدرس دهی نسبی را تعریف نماییم به فرآیند تغییر آدرس سلول ها متناسب با موقعیت مکانی فرمول ها در ساختار work sheet آدرس دهی نسبی می گویند.
مطلب پیشنهادی : نحوه مرج کردن یا ادغام سلول ها در اکسل
چه زمانی از آدرس دهی نسبی استفاده نمایم؟
کاربرد آدرس دهی نسبی وقتی است که شما میخواهید یک فرمول را برای یک محدوده از سلول ها تعریف کنید. در این حالت به جای اینکه در تک تک سلول ها بخواهید از یک فرمول که دارای یک چارچوب مشترک است استفاده نمایید فقط کافیست که فرمول مورد نظر خود را در اولین سلول وارد نموده و آن را به سایر سلولها در محدوده مورد نظر خود تعمیم بدهید.
قابل ذکر است که این نوع آدرس دهی در نرم افزار اکسل بصورت پیشفرض برای آدرس دهی سلول ها استفاده می شود .
اما گاهی نیاز پیدا می کنید که این نوع آدرس دهی را غیر فعال نمایید .
برای این منظور باید به سراغ آدرس دهی مطلق بروید .
آدرس دهی مطلق
آدرس دهی مطلق فرآیندی است که بر خلاف آدرس دهی نسبی وقتی که شما فرمول خود را در یک سلول نوشته و آن را به سایر سلولها تعمیم می دهید آدرس سلول های مورد اشاره در ساختار فرمول شما متناسب با موقعیت مکانی جدید فرمول تغییر نمی کند بلکه آدرس سلول ها حالت اولیه خود را حفظ می نماید.
به عنوان مثال در جدول مثال قبلی فرض نمایید که می خواهیم برای تمامی کالاها به میزان ۱۰ درصد تخفیف قائل شویم.
کاری که انجام می دهیم این است که سلول مربوط به E2 را برای درج میزان تخفیف مورد نظر خود در نظر میگیریم.
اکنون برای اعمال تخفیف در مبلغ نهایی فروش برای هر کالا درون سلول D2 که مربوط به کالای اول میباشد فرمول زیر را وارد میکنیم.
حال اگر این فرمول را به روش قبلی به سایر سلولها تعمیم بدهیم ملاحظه خواهید نمود که محاسبات ما فقط برای کالای اول به درستی انجام شده است و برای سایر کالاها هیچ تخفیفی اعمال نشده است.
دلیل این خطا چه می تواند باشد.؟
در جدول اطلاعات فوق اگر بر روی سلول D3 که مربوط به محاسبه مبلغ کل برای کالای دوم ما می باشد کلیک نمایید مشاهده میکنید که در قسمت Formula Bar کل مبلغ فروش ما در سلول E3 ضرب شده است در صورتی که میزان تخفیفی که ما در نظر گرفته ایم در سلول E2 قرار دارد.
بنابراین اتفاقی که میافتد این است که اکسل قسمت اول فرمول ما را به درستی محاسبه نموده و برای اعمال میزان تخفیف مورد نظر ما به سلول E3 مراجعه می نماید اما از آنجا که ما درصد تخفیف را در این سلول درج ننموده ایم اکسل دچار خطا می شود.
و این قضیه برای سایر سلول های بعدی نیز به همین منوال روی خواهد داد و محاسبات ما را دچار خطا خواهد کرد.
راه حل این مشکل چیست؟
راه حل استفاده از آدرس دهی مطلق به جای آدرس دهی نسبی در این فرمول خاص می باشد.
برای این منظور در سلول مربوط به D 2 که کل مبلغ فروش کالای پرینتر را با احتساب میزان تخفیف مورد نظر ما محاسبه می کنند فرمول زیر را وارد می نماییم.
اکنون بر طبق روال قبلی این فرمول را به سایر سلولها برای کالاهای دیگر تعمیم می دهیم.
اتفاقی که میافتد این است که ما به یک اکسل می گوییم که برای اینکه بداند مبلغ مورد نظر ما برای تخفیف به چه میزان است فارغ از اینکه فرمول ما دچار چه جابجایی هایی می شود در هر نقطه از worksheet فرمول وارد شد برای اعمال میزان تخفیف در محاسبات خود فقط و فقط به سلول E 2 مراجعه نماید.
برای اینکه آدرس دهی را از حالت نسبی به حالت مطلق در آوریم لازم است که به این آدرس یک سلول و در محل مناسب از علامت $ استفاده نماییم.
انواع آدرس دهی مطلق
در اکسل به طور کلی سه نوع آدرس دهی مطلق داریم
- آدرس دهی مطلق ردیف ها به تنهایی
- آدرس دهی مطلق ستون ها به تنهایی
- آدرس دهی مطلق سطرها و ستون ها به طور همزمان
در زیر به بیان انواع مختلف آدرس دهی مطلق میپردازیم.
برای اینکه سطرهای ما با جابجا شدن فرمول در قسمت های مختلف از صفحه کاری اکسل ثابت بمانند کافیست قبل از عدد مربوط به شماره سلول یا ردیف از علامت $ استفاده کنیم.
در این حالت با جابجا شدن فرمول ما در صفحه کاری فقط مختصات مربوط به ستون ها تغییر می کند و هیچ تغییری در آدرس مربوط به ردیف سلول ها ایجاد نمی شود.
به مثال زیر توجه کنید.
در مثال فوق ما آدرس مربوط به ردیف دوم را به حالت مطلق تعریف نموده ایم
۲ آدرس دهی مطلق ستونها به تنهایی
در این روش از آدرس دهی مطلق اگر بخواهیم موقعیت ستون های ما با جا به جا شدن فرمول در قسمتهای مختلف از صفحه کاری اکسل ثابت بماند میتوانیم قبل از حرف مربوط به ستون سلول از علامت $ استفاده کنیم.
این بار با جابجا شدن فرمول در صفحه کاری به جای تغییر مختصات ستونها فقط مختصات مربوط به سطر ها تغییر پیدا می کنند.
به مثال زیر توجه کنید.
در این مثال ما با قرار دادن علامت $ در قبل از حرف B ستون را از بطور مطلق آدرس دهی می کنیم .
آدرس دهی مطلق سطرها و ستون ها به طور همزمان
بسیاری از مواقع پیش می آید که ما نیاز پیدا میکنیم که سطرها و ستون های خود را در آدرس دهی به طور مطلق تعریف نمایید برای این منظور کافیست قبل از عدد مربوط به به ردیف از علامت$ و همینطور قبل از حرف مربوط به ستون از علامت$ استفاده کنیم.
به مثال زیر توجه کنید
در مثال فوق تمامی سطرها و ستونها بطور مطلق آدرس دهی شده اند و با جابجایی فرمول مربوط هیچ تغییری در نتیجه محاسبه برای ما ایجاد نخواهد شد
دیدگاهتان را بنویسید